package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.controller.QueryMarketShoppingGuideSalaryDocumentGroupByMonthResponse
import com.zxy.supplier_system.server.entity.MarketShoppingGuideSalaryPlanResult
import com.zxy.supplier_system.server.entity.SystemService
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query

interface MarketShoppingGuideSalaryPlanResultRepository: JpaRepository<MarketShoppingGuideSalaryPlanResult,Int> {

    fun findByMarket_SystemServiceAndMarket_IdAndYearAndMonth(
        market_systemService: SystemService,
        market_id: Int,
        year: Int,
        month: Int
    ): MarketShoppingGuideSalaryPlanResult?

    fun deleteByMarket_SystemServiceAndMarket_IdAndYearAndMonth(
        systemService: SystemService,
        marketId: Int,
        year: Int,
        month: Int
    )

    @Query(
        """
    SELECT NEW com.zxy.supplier_system.server.controller.QueryMarketShoppingGuideSalaryDocumentGroupByMonthResponse(
        r.year , 
        r.month, 
        SUM(i.salary)
    ) 
    FROM MarketShoppingGuideSalaryPlanResult r
     join r.documents d
    JOIN d.items i 
    WHERE r.market.id = ?1  
    GROUP BY r.year, r.month
    order by r.year desc ,r.month desc 
""",
        countQuery = """
    SELECT COUNT(1) 
    FROM (
        SELECT r.year as year, r.month as month
        FROM MarketShoppingGuideSalaryPlanResult r
        join r.documents d
        WHERE r.market.id = ?1  
        GROUP BY r.year, r.month
    ) tmp
"""
    )
    fun findMonthlySalarySummary(marketId:Int,pageable: Pageable): Page<QueryMarketShoppingGuideSalaryDocumentGroupByMonthResponse>

}
